



ALTER   proc sp_load_all_shipping_delayed_lots
As

declare @results Table(
	TLT_Lot_Number varchar(50),
	TLT_Customer_PO varchar(50),
	TLT_Invoice_Number varchar(50),
	TLT_Part_Number varchar(50),
	TLT_Lead_Count int,
	TLT_Package varchar(50),
	TLT_Current_Location varchar(100),
	TLT_NextLocation varchar(50),
	TLT_Ship_Date varchar(50),
	TLT_Ship_Qty int,
	TLT_MAWB_Number varchar(50),
	TLT_HAWB_Number varchar(50),
	TLT_Flight_Number varchar(50),
	TLT_ETD varchar(50),
	TLT_ETA varchar(50),
	TLT_Vendor_Name varchar(50))

declare @process_step_id int
declare @lot_number varchar(50)
declare @part_number varchar(50)
declare @vendor_name varchar(50)
declare @temp_lot_id int
declare @temp_process_step_id int
declare @current_location varchar(100)

declare	@temp_TLT_Lot_ID bigint
declare	@temp_TLT_Customer_PO varchar(50)
declare	@temp_TLT_Invoice_Number varchar(50)
declare	@temp_TLT_Lead_Count int
declare	@temp_TLT_Package varchar(50)
declare	@temp_TLT_NextLocation varchar(50)
declare	@temp_TLT_Ship_Date datetime
declare	@temp_TLT_Ship_Qty int
declare	@temp_TLT_MAWB_Number varchar(50)
declare	@temp_TLT_HAWB_Number varchar(50)
declare	@temp_TLT_Flight_Number varchar(50)
declare	@temp_TLT_ETD varchar(50)
declare	@temp_TLT_ETA varchar(50)
declare @ship_date_us_format varchar (50)

declare transitLotCursor CURSOR for
select TLT_Lot_ID, TLT_Customer_PO, TLT_Invoice_Number, TLT_Lead_Count, TLT_Package, TLT_NextLocation, TLT_Ship_Date, TLT_Ship_Qty, TLT_MAWB_Number, TLT_HAWB_Number, TLT_Flight_Number, TLT_ETD, TLT_ETA from Transit_Lot_Table where TLT_Shipped = 1

open transitLotCursor
	fetch next from transitLotCursor
	into @temp_TLT_Lot_ID, @temp_TLT_Customer_PO, @temp_TLT_Invoice_Number, @temp_TLT_Lead_Count, @temp_TLT_Package, @temp_TLT_NextLocation, @temp_TLT_Ship_Date, @temp_TLT_Ship_Qty, @temp_TLT_MAWB_Number, @temp_TLT_HAWB_Number, @temp_TLT_Flight_Number, @temp_TLT_ETD, @temp_TLT_ETA 

	set @ship_date_us_format = convert (varchar, @temp_TLT_Ship_Date, 101)
	while @@fetch_status = 0
	begin

		print 'lot id - ' + convert (varchar, @temp_tlt_lot_id)

		declare @expected_arrival_time datetime
		if @temp_TLT_ETA is not null
		begin
			set @expected_arrival_time = dateadd (day, 2, @temp_TLT_ETA)
		end

		else if @temp_TLT_ETD is not null
		begin
			set @expected_arrival_time = dateadd (day, 5, @temp_TLT_ETD)
		end

		else if @temp_TLT_Ship_Date is not null
		begin
			set @expected_arrival_time = dateadd (day, 5, @temp_TLT_Ship_Date)
		end

		else
		begin
			-- no valid arrival time
			goto SKIP_PROC
		end		

		if @expected_arrival_time > getdate()
		begin
			print 'arrival time =' + convert (varchar, @expected_arrival_time)
			goto SKIP_PROC
		end
		
		select @process_step_id = processStep_id, @lot_number = lot_number from lot_table where lot_id = @temp_TLT_Lot_ID
		select @part_number = Part_Number from product where processStep_id = @process_step_id

		declare processStepIdCursor CURSOR for
		select processStep_ID from fn_get_process_flow_forward_by_id (@process_step_id)
		
		open processStepIdCursor
		fetch next from processStepIdCursor
		into @temp_process_step_id 

		while @@fetch_status = 0
		begin
			if @temp_process_step_id = @process_step_id
			begin
				-- skip same processStep ID
				goto SKIP_LOCAL_PROC
			end
			
			select @temp_lot_id = lot_id from lot_table where lot_number = @lot_number and processStep_id = @temp_process_step_id
			if @temp_lot_id is not null
			begin
				-- Lot has moved to the next location
				print 'previous step id = ' + convert (varchar, @process_step_id) + ', next step id = ' + convert (varchar, @temp_process_step_id)
				print 'Updating ' + convert (varchar, @temp_lot_id)	
				update transit_lot_table set TLT_Shipped = 0 where TLT_Lot_ID = @temp_TLT_Lot_ID
			end

			else 
			begin
				select @current_location = a.City + '/' + l.L_Vendor_Name, @vendor_name = l.L_Vendor_Name from Location l, Address a, Lot_Table lt where (lt.location_id = l.location_id) and (l.address_id = a.address_id) and lt.lot_id = @temp_TLT_Lot_ID

				insert into @results (TLT_Lot_Number, TLT_Customer_PO, TLT_Invoice_Number, TLT_Part_Number, TLT_Lead_Count, TLT_Package, TLT_Current_Location, TLT_NextLocation, TLT_Ship_Date, TLT_Ship_Qty, TLT_MAWB_Number, TLT_HAWB_Number, TLT_Flight_Number, TLT_ETD, TLT_ETA, TLT_Vendor_Name) values (@lot_number, @temp_TLT_Customer_PO, @temp_TLT_Invoice_Number, @part_number, @temp_TLT_Lead_Count, @temp_TLT_Package, @current_location, @temp_TLT_NextLocation, convert (varchar, @temp_TLT_Ship_Date, 101), @temp_TLT_Ship_Qty, @temp_TLT_MAWB_Number, @temp_TLT_HAWB_Number, @temp_TLT_Flight_Number, @temp_TLT_ETD, @temp_TLT_ETA, @vendor_name)
				break
			end

			SKIP_LOCAL_PROC:
			fetch next from processStepIdCursor
			into @temp_process_step_id
		end
	
		close processStepIdCursor
		deallocate processStepIdCursor

		SKIP_PROC:
		fetch next from transitLotCursor
		into @temp_TLT_Lot_ID, @temp_TLT_Customer_PO, @temp_TLT_Invoice_Number, @temp_TLT_Lead_Count, @temp_TLT_Package, @temp_TLT_NextLocation, @temp_TLT_Ship_Date, @temp_TLT_Ship_Qty, @temp_TLT_MAWB_Number, @temp_TLT_HAWB_Number, @temp_TLT_Flight_Number, @temp_TLT_ETD, @temp_TLT_ETA 
	end

close transitLotCursor
deallocate transitLotCursor

select *from @results					
return


